<?php

class Registro_model extends CI_Model {

    function __construct() {
        parent::__construct();
    }

    public function buscaSectores() {
        $sql = "select * from sectores order by sector";
        return $this->db->query($sql);
    }

    public function buscaDepartamento() {
        $sql = "select * from departamentos where dptEstado = 'A' order by departamento";
        return $this->db->query($sql);
    }

    public function buscaCiudad($dep) {
        $sql = "select * from ciudades where dptCod = '$dep' and ciudadEstado = 'A' order by ciudad";
        return $this->db->query($sql);
    }

    public function guarda($rolId, $accion = Null) {
        $result = "";
        $validaExtra = ($accion == "EDITA") ? " and usuarioId <> '{$this->input->post('usuarioId')}'" : "";
        //valida que no exista el usuario
        $sql = "select * from usuarios where usuario = '{$this->input->post('email')}' $validaExtra";
        $query = $this->db->query($sql);
        if ($this->db->query($sql)->num_rows() > 0)
            return "0";
        //Valida en la tabla correspondiente
        switch ($rolId) {
            case '2'://Empresa
                //valida que la empresa no exista ya en el sistema
                $validaExtra = ($accion == "EDITA") ? " and empresaId <> '{$this->input->post('empId')}'" : "";
                $sql = "select * from empresas where empresaNit = '{$this->input->post('nit')}' $validaExtra";
                if ($this->db->query($sql)->num_rows() > 0)
                    return "1";
                break;
            case '3'://Institucion
                //Valida que la institucion no exista en el sistema
                $validaExtra = ($accion == "EDITA") ? " and institucionId <> '{$this->input->post('instiId')}'" : "";
                $sql = "select * from instituciones where institucionNit = '{$this->input->post('nit')}' $validaExtra";
                if ($this->db->query($sql)->num_rows() > 0)
                    return "1";
                break;
            case '4'://Persona
                //Valida que la persona no exista en el sistema
                $validaExtra = ($accion == "EDITA") ? " and personasId <> '{$this->input->post('personaId')}'" : "";
                $sql = "select * from personas where personaDocumento = '{$this->input->post('nit')}' and personaTipoDocumento = '{$this->input->post('tipDoc')}' $validaExtra";
                if ($this->db->query($sql)->num_rows() > 0)
                    return "1";
                break;
            default:
                break;
        }
        $this->db->trans_start(); //inicia la transaccion
        if ($accion == "") {
            //FASE DE INSERTS DESPUES DE LAS VALIDACIONES
            //Guarda primero el usuario
            $sql = "insert into usuarios(usuario,usuarioClave,rolId) values(";
            $sql .= "'{$this->input->post('email')}',md5('{$this->input->post('passwd')}'),'{$rolId}')";
            $this->db->query($sql);
            $lastId = $this->db->insert_id();
            //Guarda en la tabla correspondiente
            switch ($rolId) {
                case '2'://Empresa
                    $sql = "insert into empresas(empresaRazon,empresaNit,empresaTelefono1,empresaDireccion,empresaPagina,empresaContacto,empresaFechaCreo,sectorId,ciudadId,usuarioId,empresaExpDiscapacidados) values(";
                    $sql .= "'{$this->input->post('razon')}','{$this->input->post('nit')}','{$this->input->post('telefonos')}','{$this->input->post('direccion')}','{$this->input->post('pagina')}','{$this->input->post('contacto')}',now(),'{$this->input->post('sector')}','{$this->input->post('ciudad')}','$lastId','{$this->input->post('expPersonas')}')";
                    $this->db->query($sql);
                    break;
                case '3'://Institucion
                    $formacion = $this->input->post('formacion');
                    $sql = "insert into instituciones(institucionRazon,institucionNit,institucionTelefono,institucionDireccion,institucionPagina,institucionContacto,institucionFechaCreo,ciudadId,usuarioId,`institucionPersonasEquipo`) values(";
                    $sql .= "'{$this->input->post('razon')}','{$this->input->post('nit')}','{$this->input->post('telefonos')}','{$this->input->post('direccion')}','{$this->input->post('pagina')}','{$this->input->post('contacto')}',now(),'{$this->input->post('ciudad')}','$lastId','{$this->input->post('perEquipo')}')";
                    $this->db->query($sql);
                    //Inicia el proceso para insertar los servicios que presta
                    $instiTmpId = $this->db->insert_id();
                    if (is_array($formacion)) {
                        foreach ($formacion as $serviInclu) {
                            $sql = "insert into instituciones_por_serviciosinclusion(`institucionId`,`servInclusionId`) values ('$instiTmpId','$serviInclu')";
                            $this->db->query($sql);
                        } //fin foreach($_POST["formacion"] as $serviInclu){
                    }
                    break;
                case '4'://Persona
                    $tran = $this->input->post("transaccion");
                    $sql = "insert into personas(personaDocumento,personaTipoDocumento,personaCompetencias,personaNivelAcademico,personaNombres,personaApellidos,personaGenero,personaFechaNac,personaEstadoCivil,personaPersonaACargo,personaEstrato,ciudadId,personaAspiracion,personaFechaCreo,usuarioId,personaAspiracionTope,personaTelefono,`personaCertificadoJunta`,`personaCelular`,personaInstituciones) values(";
                    $sql .= "'{$this->input->post('nit')}','{$this->input->post('tipDoc')}','{$this->input->post('competencias')}','{$this->input->post('nivelAca')}','{$this->input->post('nombre')}','{$this->input->post('apellido')}','{$this->input->post('genero')}','{$this->input->post('fecNac')}','{$this->input->post('estadoCivil')}','{$this->input->post('perCargo')}','{$this->input->post('estrato')}','{$this->input->post('ciudad')}','{$this->input->post('aspiracion')}',now(),'$lastId','{$this->input->post('aspiracionTop')}','{$this->input->post("telefono")}','{$this->input->post("certificadoInva")}','{$this->input->post("celular")}','{$this->input->post("institucionRela")}')";
                    $this->db->query($sql);
                    $personaId = $this->db->insert_id();
                    //Revisa si ingreso experiencia Laboral
                    if ($this->countTemporal("exp", $tran) > 0) {
                        $sql = "select tempVarchar1,tempVarchar2,tempVarchar3,tempVarchar4,tempVarchar5,tempVarchar6,tempVarchar7,tempDate1,tempDate2,tempText1 from temporal where transaccion = '$tran' and tempInd = 'exp'";
                        $query = $this->db->query($sql);
                        foreach ($query->result() as $value) {
                            $tipContrato = explode("-", $value->tempVarchar7);
                            $sql = "insert into explaboral(expLaboralEmpresa,expLaboralContacto,expLaboralTelefono,expLaboralInicio,expLaboralFin,expLaboralFunciones,expLaboralActual,personasId,`tipContraId`,`expLaboralVinculacion`,`expLaboralPagos`) values (";
                            $sql .= "'{$value->tempVarchar1}','{$value->tempVarchar3}','{$value->tempVarchar4}','{$value->tempDate1}','{$value->tempDate2}','{$value->tempText1}','{$value->tempVarchar2}','$personaId','{$value->tempVarchar7}','{$value->tempVarchar5}','{$value->tempVarchar6}')";
                            $this->db->query($sql);
                        }
                    }
                    //Revisa si ingreso Información Académica
                    if ($this->countTemporal("aca", $tran) > 0) {
                        $sql = "select tempVarchar1,tempVarchar2,`tempVarchar6`,tempVarchar4,tempVarchar3,tempVarchar5,tempDate1,tempDate2 from temporal temp                                  
                                where transaccion = '$tran' and tempInd = 'aca'";
                        $query = $this->db->query($sql);
                        foreach ($query->result() as $value) {
                            $sql = "insert into formacademicas(formAcademicaUltima,tipFormacionId,formAcademicaInstitucion,formAcademicaTitulo,formAcademicaInicio,formAcademicaFin,formAcademicaActual,personasId) values (";
                            $sql .= "'{$value->tempVarchar2}','{$value->tempVarchar3}','{$value->tempVarchar1}','{$value->tempVarchar4}','{$value->tempDate1}','{$value->tempDate2}','{$value->tempVarchar5}','$personaId')";
                            $this->db->query($sql);
                        }
                    }
                    //Revisa si ingreso Discapacidades
                    if ($this->countTemporal("discapacidad", $tran) > 0) {
                        $sql = "select tempVarchar1,tempText1,tempText2 from temporal where transaccion = '$tran' and tempInd = 'discapacidad'";
                        $query = $this->db->query($sql);
                        foreach ($query->result() as $value) {
                            $sql = "insert into discapacidades(discapacidadDesc,tipDiscapacidadId,discapacidadCausa,personasId) values (";
                            $sql .= "'{$value->tempText1}','{$value->tempVarchar1}','{$value->tempText2}','$personaId')";
                            $this->db->query($sql);
                        }
                    }
                    //Revisa si ingreso Ayudas Tecnicas
                    if ($this->countTemporal("ayudasTecnicas", $tran) > 0) {
                        $sql = "select tempVarchar2,tempText1 from temporal where transaccion = '$tran' and tempInd = 'ayudasTecnicas'";
                        $query = $this->db->query($sql);
                        foreach ($query->result() as $value) {
                            $sql = "insert into ayudastecnicas(tipAyudaTecnicaId,ayudaTecnicaComentario,personasId) values (";
                            $sql .= "'{$value->tempVarchar2}','{$value->tempText1}','$personaId')";
                            $this->db->query($sql);
                        }
                    }
                    //Revisa si ingreso Seguridad Social
                    if ($this->countTemporal("administraPersonas", $tran) > 0) {
                        $sql = "select tempInt1,tempVarchar2,tempDate1,tempDate2,tempVarchar3,tempInt2,tempVarchar4,tempVarchar5 from temporal where transaccion = '$tran' and tempInd = 'administraPersonas'";
                        $query = $this->db->query($sql);
                        foreach ($query->result() as $value) {
                            $sql = "insert into adminispersona(administradoraId,adminPersonaContrato,adminPersonaFecIni,adminPersonaFecFin,adminPersonaActual,personasId,`adminPersonaTiempoPension`,`adminPersonaRegimen`,`adminPersonaPension`) values (";
                            $sql .= "'{$value->tempInt1}','{$value->tempVarchar2}','{$value->tempDate1}','{$value->tempDate2}','{$value->tempVarchar3}','$personaId','{$value->tempInt2}','{$value->tempVarchar4}','{$value->tempVarchar5}')";
                            $this->db->query($sql);
                        }
                    }
                    $sql = "delete from temporal where transaccion = '$tran'";
                    $this->db->query($sql);
                    $result = "ultima|$personaId";
                    break;
            }
        }//fin if($accion == ""){
        elseif ($accion == "EDITA") {
            //Guarda primero el usuario
            if ($this->input->post("editaInfo") != "Si") {
                $sql = "update usuarios set usuario = '{$this->input->post('email')}' where usuarioId = '{$this->input->post('usuarioId')}'";
                $this->db->query($sql);
            }
            switch ($rolId) {
                case '2'://Empresa
                    $sql = "update empresas set ";
                    $sql .= "empresaRazon   = '{$this->input->post('razon')}',";
                    $sql .= "empresaNit     = '{$this->input->post('nit')}',";
                    $sql .= "empresaTelefono1= '{$this->input->post('telefonos')}',";
                    $sql .= "empresaDireccion= '{$this->input->post('direccion')}',";
                    $sql .= "empresaPagina  = '{$this->input->post('pagina')}',";
                    $sql .= "empresaContacto= '{$this->input->post('contacto')}',";
                    $sql .= "sectorId       = '{$this->input->post('sector')}',";
                    $sql .= "empresaExpDiscapacidados  = '{$this->input->post('expPersonas')}',";
                    $sql .= "ciudadId       = '{$this->input->post('ciudad')}' where empresaId = '{$this->input->post('empId')}'";
                    $this->db->query($sql);
                    break;
                case '3'://Institucion
                    $formacion = $this->input->post('formacion');
                    $sql = "update instituciones set ";
                    $sql .= "institucionRazon       = '{$this->input->post('razon')}',";
                    $sql .= "institucionNit         = '{$this->input->post('nit')}',";
                    $sql .= "institucionTelefono    = '{$this->input->post('telefonos')}',";
                    $sql .= "institucionDireccion   = '{$this->input->post('direccion')}',";
                    $sql .= "institucionPagina      = '{$this->input->post('pagina')}',";
                    $sql .= "institucionContacto    = '{$this->input->post('contacto')}',";
                    $sql .= "institucionPersonasEquipo = '{$this->input->post('perEquipo')}',";
                    $sql .= "ciudadId = '{$this->input->post('ciudad')}' where institucionId = '{$this->input->post('instiId')}'";
                    $this->db->query($sql);
                    /* Elimina todos los servicios que tenia registrados para ingresar los nuevos */
                    $sql = "delete from instituciones_por_serviciosinclusion where `institucionId` = '{$this->input->post('instiId')}'";
                    $this->db->query($sql);
                    if (is_array($formacion)) {
                        /* Inserta los nuevos servicios */
                        foreach ($formacion as $serviInclu) {
                            $sql = "insert into instituciones_por_serviciosinclusion(`institucionId`,`servInclusionId`) values ('{$this->input->post('instiId')}','$serviInclu')";
                            $this->db->query($sql);
                        } //fin foreach($_POST["formacion"] as $serviInclu){
                    }
                    break;
                case '4'://Persona
                    $tran = $this->input->post("transaccion");
                    $tmp = ($this->session->userdata("rolId") == "4") ? "Si" : "No";
                    $sql = "update personas set ";
                    $sql .= "personaDocumento = '{$this->input->post('nit')}',";
                    $sql .= "personaTipoDocumento = '{$this->input->post('tipDoc')}',";
                    $sql .= "personaCompetencias = '{$this->input->post('competencias')}',";
                    $sql .= "personaNivelAcademico = '{$this->input->post('nivelAca')}',";
                    $sql .= "personaNombres = '{$this->input->post('nombre')}',";
                    $sql .= "personaApellidos = '{$this->input->post('apellido')}',";
                    $sql .= "personaGenero = '{$this->input->post('genero')}',";
                    $sql .= "personaFechaNac = '{$this->input->post('fecNac')}',";
                    $sql .= "personaEstadoCivil = '{$this->input->post('estadoCivil')}',";
                    $sql .= "personaPersonaACargo = '{$this->input->post('perCargo')}',";
                    $sql .= "personaEstrato = '{$this->input->post('estrato')}',";
                    $sql .= "personaAspiracion = '{$this->input->post('aspiracion')}',";
                    $sql .= "personaTelefono = '{$this->input->post('telefono')}',";
                    $sql .= "personaAspiracionTope = '{$this->input->post('aspiracionTop')}',";
                    $sql .= "personaCertificadoJunta = '{$this->input->post("certificadoInva")}',";
                    $sql .= "personaCelular = '{$this->input->post("celular")}',";
                    $sql .= "personaEditoDatos = '$tmp',";
                    $sql .= "personaInstituciones = '{$this->input->post("institucionRela")}',";
                    $sql .= "ciudadId = '{$this->input->post('ciudad')}' where personasId = '{$this->input->post('personaId')}'";
                    $this->db->query($sql);
                    $personaId = $this->input->post('personaId');
                    //Revisa si ingreso experiencia Laboral
                    if ($this->countTemporal("exp", $tran) > 0) {
                        $sql = "delete from explaboral where personasId = '$personaId'";
                        $this->db->query($sql);
                        $sql = "select tempVarchar1,tempVarchar2,tempVarchar3,tempVarchar4,tempDate1,tempDate2,tempText1,tempVarchar5,tempVarchar6,tempVarchar7 from temporal where transaccion = '$tran' and tempInd = 'exp'";
                        $query = $this->db->query($sql);
                        foreach ($query->result() as $value) {
                            $tmp = (is_int($value->tempVarchar7))?$value->tempVarchar7:"NULL";
                            $sql = "insert into explaboral(expLaboralEmpresa,expLaboralContacto,expLaboralTelefono,expLaboralInicio,expLaboralFin,expLaboralFunciones,expLaboralActual,personasId,`tipContraId`,`expLaboralVinculacion`,`expLaboralPagos`) values (";
                            $sql .= "'{$value->tempVarchar1}','{$value->tempVarchar3}','{$value->tempVarchar4}','{$value->tempDate1}','{$value->tempDate2}','{$value->tempText1}','{$value->tempVarchar2}','$personaId',$tmp,'{$value->tempVarchar5}','{$value->tempVarchar6}')";
                            $this->db->query($sql);
                        }
                    }
                    //Revisa si ingreso Información Académica
                    if ($this->countTemporal("aca", $tran) > 0) {
                        $sql = "delete from formacademicas where personasId = '$personaId'";
                        $this->db->query($sql);
                        $sql = "select tempVarchar1,tempVarchar2,`tempVarchar6`,tempVarchar4,tempVarchar3,tempVarchar5,tempDate1,tempDate2,inst.`institucionRazon` from temporal temp 
                                left join instituciones inst on inst.`institucionId` = temp.`tempVarchar1` 
                                where transaccion = '$tran' and tempInd = 'aca'";
                        $query = $this->db->query($sql);
                        foreach ($query->result() as $value) {
                            $sql = "insert into formacademicas(formAcademicaUltima,tipFormacionId,formAcademicaInstitucion,formAcademicaTitulo,formAcademicaInicio,formAcademicaFin,formAcademicaActual,personasId) values (";
                            $sql .= "'{$value->tempVarchar2}','{$value->tempVarchar3}','{$value->tempVarchar1}','{$value->tempVarchar4}','{$value->tempDate1}','{$value->tempDate2}','{$value->tempVarchar5}','$personaId')";
                            $this->db->query($sql);
                        }
                    }
                    //Revisa si ingreso Discapacidades
                    if ($this->countTemporal("discapacidad", $tran) > 0) {
                        $sql = "delete from discapacidades where personasId = '$personaId'";
                        $this->db->query($sql);
                        $sql = "select tempVarchar1,tempText1,tempText2 from temporal where transaccion = '$tran' and tempInd = 'discapacidad'";
                        $query = $this->db->query($sql);
                        foreach ($query->result() as $value) {
                            $sql = "insert into discapacidades(discapacidadDesc,tipDiscapacidadId,discapacidadCausa,personasId) values (";
                            $sql .= "'{$value->tempText1}','{$value->tempVarchar1}','{$value->tempText2}','$personaId')";
                            $this->db->query($sql);
                        }
                    }
                    //Revisa si ingreso Ayudas Tecnicas
                    if ($this->countTemporal("ayudasTecnicas", $tran) > 0) {
                        $sql = "delete from ayudastecnicas where personasId = '$personaId'";
                        $this->db->query($sql);
                        $sql = "select tempVarchar2,tempText1 from temporal where transaccion = '$tran' and tempInd = 'ayudasTecnicas'";
                        $query = $this->db->query($sql);
                        foreach ($query->result() as $value) {
                            $sql = "insert into ayudastecnicas(tipAyudaTecnicaId,ayudaTecnicaComentario,personasId) values (";
                            $sql .= "'{$value->tempVarchar2}','{$value->tempText1}','$personaId')";
                            $this->db->query($sql);
                        }
                    }
                    //Revisa si ingreso Seguridad Social
                    if ($this->countTemporal("administraPersonas", $tran) > 0) {
                        $sql = "delete from adminispersona where personasId = '$personaId'";
                        $this->db->query($sql);
                        $sql = "select tempInt1,tempVarchar2,tempDate1,tempDate2,tempVarchar3,tempInt2,tempVarchar4,tempVarchar5 from temporal where transaccion = '$tran' and tempInd = 'administraPersonas'";
                        $query = $this->db->query($sql);
                        foreach ($query->result() as $value) {
                            $sql = "insert into adminispersona(administradoraId,adminPersonaContrato,adminPersonaFecIni,adminPersonaFecFin,adminPersonaActual,personasId,`adminPersonaTiempoPension`,`adminPersonaRegimen`,`adminPersonaPension`) values (";
                            $sql .= "'{$value->tempInt1}','{$value->tempVarchar2}','{$value->tempDate1}','{$value->tempDate2}','{$value->tempVarchar3}','$personaId','{$value->tempInt2}','{$value->tempVarchar4}','{$value->tempVarchar5}')";
                            $this->db->query($sql);
                        }
                    }
                    $sql = "delete from temporal where transaccion = '$tran'";
                    $this->db->query($sql);
                    $result = "ultima|$personaId";
                    break;
            }
        }
        $this->db->trans_complete();
        return $result;
    }

    public function guardaPersonaAfiliada($rolId, $accion = Null) {
        $result = "";
        //valida que no exista el usuario
        $sql = "select * from usuarios where usuario = '{$this->input->post('email')}' and usuarioId <> '{$this->input->post('usuarioId')}'";
        $query = $this->db->query($sql);
        if ($this->db->query($sql)->num_rows() > 0)
            return "0";
        //Valida que la persona no exista en el sistema
        $sql = "select * from personas where personaDocumento = '{$this->input->post('nit')}' and personaTipoDocumento = '{$this->input->post('tipDoc')}'  and personasId <> '{$this->input->post('personaId')}'";
        if ($this->db->query($sql)->num_rows() > 0)
            return "1";
        $this->db->trans_start(); //inicia la transaccion
        //Guarda primero el usuario
        if ($this->input->post("editaInfo") != "Si") {
            $sql = "update usuarios set usuario = '{$this->input->post('email')}' where usuarioId = '{$this->input->post('usuarioId')}'";
            $this->db->query($sql);
        }
        $tran = $this->input->post("transaccion");
        $tmp = ($this->session->userdata("rolId") == "4") ? "Si" : "No";
        $sql = "update personas set ";
        $sql .= "personaDocumento = '{$this->input->post('nit')}',";
        $sql .= "personaTipoDocumento = '{$this->input->post('tipDoc')}',";
        $sql .= "personaCompetencias = '{$this->input->post('competencias')}',";
        $sql .= "personaNivelAcademico = '{$this->input->post('nivelAca')}',";
        $sql .= "personaNombres = '{$this->input->post('nombre')}',";
        $sql .= "personaApellidos = '{$this->input->post('apellido')}',";
        $sql .= "personaGenero = '{$this->input->post('genero')}',";
        $sql .= "personaFechaNac = '{$this->input->post('fecNac')}',";
        $sql .= "personaEstadoCivil = '{$this->input->post('estadoCivil')}',";
        $sql .= "personaPersonaACargo = '{$this->input->post('perCargo')}',";
        $sql .= "personaEstrato = '{$this->input->post('estrato')}',";
        $sql .= "personaAspiracion = '{$this->input->post('aspiracion')}',";
        $sql .= "personaTelefono = '{$this->input->post('telefono')}',";
        $sql .= "personaAspiracionTope = '{$this->input->post('aspiracionTop')}',";
        $sql .= "personaCertificadoJunta = '{$this->input->post("certificadoInva")}',";
        $sql .= "personaCelular = '{$this->input->post("celular")}',";
        $sql .= "personaEditoDatos = '$tmp',";
        $sql .= "personaInstituciones = '{$this->input->post("institucionRela")}',";
        $sql .= "ciudadId = '{$this->input->post('ciudad')}' where personasId = '{$this->input->post('personaId')}'";
        $this->db->query($sql);
        $personaId = $this->input->post('personaId');
        //Revisa si ingreso experiencia Laboral
        if ($this->countTemporal("exp", $tran) > 0) {
            $sql = "delete from explaboral where personasId = '$personaId'";
            $this->db->query($sql);
            $sql = "select tempVarchar1,tempVarchar2,tempVarchar3,tempVarchar4,tempDate1,tempDate2,tempText1,tempVarchar5,tempVarchar6,tempVarchar7 from temporal where transaccion = '$tran' and tempInd = 'exp'";
            $query = $this->db->query($sql);
            foreach ($query->result() as $value) {
                $sql = "insert into explaboral(expLaboralEmpresa,expLaboralContacto,expLaboralTelefono,expLaboralInicio,expLaboralFin,expLaboralFunciones,expLaboralActual,personasId,`tipContraId`,`expLaboralVinculacion`,`expLaboralPagos`) values (";
                $sql .= "'{$value->tempVarchar1}','{$value->tempVarchar3}','{$value->tempVarchar4}','{$value->tempDate1}','{$value->tempDate2}','{$value->tempText1}','{$value->tempVarchar2}','$personaId','{$value->tempVarchar7}','{$value->tempVarchar5}','{$value->tempVarchar6}')";
                $this->db->query($sql);
            }
        }
        //Revisa si ingreso Información Académica
        if ($this->countTemporal("aca", $tran) > 0) {
            $sql = "delete from formacademicas where personasId = '$personaId'";
            $this->db->query($sql);
            $sql = "select tempVarchar1,tempVarchar2,`tempVarchar6`,tempVarchar4,tempVarchar3,tempVarchar5,tempDate1,tempDate2,inst.`institucionRazon` from temporal temp 
                                left join instituciones inst on inst.`institucionId` = temp.`tempVarchar1` 
                                where transaccion = '$tran' and tempInd = 'aca'";
            $query = $this->db->query($sql);
            foreach ($query->result() as $value) {
                $institucion = ($value->tempVarchar6 != "") ? $value->tempVarchar1 : $value->institucionRazon;
                $sql = "insert into formacademicas(formAcademicaUltima,tipFormacionId,formAcademicaInstitucion,formAcademicaTitulo,formAcademicaInicio,formAcademicaFin,formAcademicaActual,personasId) values (";
                $sql .= "'{$value->tempVarchar2}','{$value->tempVarchar3}','$institucion','{$value->tempVarchar4}','{$value->tempDate1}','{$value->tempDate2}','{$value->tempVarchar5}','$personaId')";
                $this->db->query($sql);
            }
        }
        //Revisa si ingreso Discapacidades
        if ($this->countTemporal("discapacidad", $tran) > 0) {
            $sql = "delete from discapacidades where personasId = '$personaId'";
            $this->db->query($sql);
            $sql = "select tempVarchar1,tempText1,tempText2 from temporal where transaccion = '$tran' and tempInd = 'discapacidad'";
            $query = $this->db->query($sql);
            foreach ($query->result() as $value) {
                $sql = "insert into discapacidades(discapacidadDesc,tipDiscapacidadId,discapacidadCausa,personasId) values (";
                $sql .= "'{$value->tempText1}','{$value->tempVarchar1}','{$value->tempText2}','$personaId')";
                $this->db->query($sql);
            }
        }
        //Revisa si ingreso Ayudas Tecnicas
        if ($this->countTemporal("ayudasTecnicas", $tran) > 0) {
            $sql = "delete from ayudastecnicas where personasId = '$personaId'";
            $this->db->query($sql);
            $sql = "select tempVarchar2,tempText1 from temporal where transaccion = '$tran' and tempInd = 'ayudasTecnicas'";
            $query = $this->db->query($sql);
            foreach ($query->result() as $value) {
                $sql = "insert into ayudastecnicas(tipAyudaTecnicaId,ayudaTecnicaComentario,personasId) values (";
                $sql .= "'{$value->tempVarchar2}','{$value->tempText1}','$personaId')";
                $this->db->query($sql);
            }
        }
        //Revisa si ingreso Seguridad Social
        if ($this->countTemporal("administraPersonas", $tran) > 0) {
            $sql = "delete from adminispersona where personasId = '$personaId'";
            $this->db->query($sql);
            $sql = "select tempInt1,tempVarchar2,tempDate1,tempDate2,tempVarchar3,tempInt2,tempVarchar4,tempVarchar5 from temporal where transaccion = '$tran' and tempInd = 'administraPersonas'";
            $query = $this->db->query($sql);
            foreach ($query->result() as $value) {
                $sql = "insert into adminispersona(administradoraId,adminPersonaContrato,adminPersonaFecIni,adminPersonaFecFin,adminPersonaActual,personasId,`adminPersonaTiempoPension`,`adminPersonaRegimen`,`adminPersonaPension`) values (";
                $sql .= "'{$value->tempInt1}','{$value->tempVarchar2}','{$value->tempDate1}','{$value->tempDate2}','{$value->tempVarchar3}','$personaId','{$value->tempInt2}','{$value->tempVarchar4}','{$value->tempVarchar5}')";
                $this->db->query($sql);
            }
        }
        $sql = "delete from temporal where transaccion = '$tran'";
        $this->db->query($sql);
        $result = "ultima|$personaId";

        $this->db->trans_complete();
        return $result;
    }

    public function buscaAcademico() {
        $sql = "select * from tipformacion order by tipFormacionId";
        return $this->db->query($sql);
    }

    public function guardaExpLab($opc, $tran = NULL) {
        $tran = (isset($tran)) ? $tran : $this->input->post('transaccion');
        switch ($opc) {
            case 'C':
                /*
                 *   tempVarchar1 = nombre empresa
                 *   tempVarchar2 = trabajo actual
                 *   tempVarchar3 = contacto empresa
                 *   tempVarchar4 = telefono contacto empresa
                 *   tempDate1 = fecha Inicial
                 *   tempDate2 = fecha final
                 *   tempText1 = funciones que realiza
                 *   tempVarchar7 = Tipo de contrato
                 *   tempVarchar5 = Vinculo
                 *   tempVarchar6 = Pagos que le realizaron
                 */
                $funciones = trim($this->input->post('funciones'));
                $tipContrato = explode("-", $this->input->post('tipContrato'));
                $pagos = ($tipContrato[1] == "Si") ? trim($this->input->post('prestaciones'), ",") : "";
                $sql = "insert into temporal(tempVarchar1,tempVarchar2,tempVarchar3,tempVarchar4,tempDate1,tempDate2,tempText1,transaccion,tempInd,`tempVarchar7`,`tempVarchar5`,`tempVarchar6`) values(";
                $sql .= "'{$this->input->post('nombre')}','{$this->input->post('actual')}','{$this->input->post('contacto')}','{$this->input->post('tel')}','{$this->input->post('fecIni')}','{$this->input->post('fecFin')}','$funciones','{$this->input->post('transaccion')}','exp','{$this->input->post('tipContrato')}','{$this->input->post('vinculo')}','$pagos')";
                $this->db->query($sql);
                return;
                break;
            case 'E':
                $sql = "select tempVarchar1,tempVarchar2,tempVarchar3,tempVarchar4,tempVarchar5,tempVarchar6,`tempVarchar7`,tempDate1,tempDate2,tempText1,tempId from temporal where tempId = '$tran'";
                $query = $this->db->query($sql);
                $obj = $query->result();
                echo json_encode($obj[0]);
                break;
            case 'A': //actualiza el registro
                $funciones = trim($this->input->post('funciones'));
                $tipContrato = explode("-", $this->input->post('tipContrato'));
                $pagos = ($tipContrato[1] == "Si") ? trim($this->input->post('prestaciones'), ",") : "";
                $sql = "update temporal set tempVarchar1 = '{$this->input->post('nombre')}',";
                $sql .= "tempVarchar2 = '{$this->input->post('actual')}',";
                $sql .= "tempVarchar3 = '{$this->input->post('contacto')}',";
                $sql .= "tempVarchar4 = '{$this->input->post('tel')}',";
                $sql .= "tempVarchar5 = '{$this->input->post('vinculo')}',";
                $sql .= "tempVarchar6 = '$pagos',";
                $sql .= "tempVarchar7 = '{$this->input->post('tipContrato')}',";
                $sql .= "tempDate1    = '{$this->input->post('fecIni')}',";
                $sql .= "tempDate2    = '{$this->input->post('fecFin')}',";
                $sql .= "tempText1    = '$funciones' where tempId = '{$this->input->post('tempId')}'";
                $this->db->query($sql);
                return;
                break;
            case 'D': //actualiza el registro
                $sql = "delete from temporal where tempId = '$tran'";
                $this->db->query($sql);
                break;
            default:
                break;
        }
    }

    public function estudios($opc, $tran = NULL) {
        $tran = (isset($tran)) ? $tran : $this->input->post('transaccion');
        switch ($opc) {
            case 'C':
                $titulo = trim($this->input->post('titulo'));
                $institucion = trim($this->input->post('institucion'));
                /*  tempVarchar1 = institucion
                 *  tempVarchar2 = ultimo nivel cursado
                 *  tempVarchar3 = formacion academica
                 *  tempVarchar4 = titulo
                 *  tempVarchar5 = estudio terminado
                 *  tempVarchar6 = otra institucion
                 */
                $sql = "insert into temporal(tempVarchar1,tempVarchar2,tempVarchar3,tempVarchar4,tempVarchar5,`tempVarchar6`,tempDate1,tempDate2,transaccion,tempInd) values(";
                $sql .= "'$institucion','{$this->input->post('ultCurso')}','{$this->input->post('formacion')}','$titulo','{$this->input->post('actual')}','{$this->input->post('otrInstitucion')}','{$this->input->post('fecIni')}','{$this->input->post('fecFin')}','{$this->input->post('transaccion')}','aca')";
                $this->db->query($sql);
                break;
            case 'E':
                $sql = "select tempVarchar1,tempVarchar2,tempVarchar3,tempVarchar4,tempVarchar5,tempDate1,tempDate2,tempId,`tempVarchar6` from temporal where tempId = '$tran'";
                $query = $this->db->query($sql);
                $obj = $query->result();
                echo json_encode($obj[0]);
                break;
            case 'A': //actualiza el registro
                $titulo = trim($this->input->post('titulo'));
                $institucion = trim($this->input->post('institucion'));
                $sql = "update temporal set tempVarchar1 = '$institucion',";
                $sql .= "tempVarchar2 = '{$this->input->post('ultCurso')}',";
                $sql .= "tempVarchar3 = '{$this->input->post('formacion')}',";
                $sql .= "tempVarchar4 = '$titulo',";
                $sql .= "tempVarchar5 = '{$this->input->post('actual')}',";
                $sql .= "tempDate1    = '{$this->input->post('fecIni')}',";
                $sql .= "tempDate2    = '{$this->input->post('fecFin')}' where tempId = '{$this->input->post('tempId')}'";
                $this->db->query($sql);
                break;
            case 'D': //actualiza el registro
                $sql = "delete from temporal where tempId = '$tran'";
                $this->db->query($sql);
                break;
            default:
                break;
        }
    }

    public function discapacidad($opc, $tran = NULL) {
        $tran = (isset($tran)) ? $tran : $this->input->post('transaccion');
        switch ($opc) {
            case 'C':
                $disca = trim($this->input->post('descripcion'));
                $causa = trim($this->input->post('causa'));
                $sql = "insert into temporal(tempVarchar1,tempText1,tempText2,transaccion,tempInd) values(";
                $sql .= "'{$this->input->post('tipo')}','$disca','$causa','{$this->input->post('transaccion')}','discapacidad')";
                $this->db->query($sql);
                return;
                break;
            case 'E':
                $sql = "select tempVarchar1,tempVarchar2,tempText1,tempText2,transaccion,tempId from temporal where tempId = '$tran'";
                $query = $this->db->query($sql);
                $obj = $query->result();
                echo json_encode($obj[0]);
                return;
                break;
            case 'A': //actualiza el registro
                $disca = trim($this->input->post('descripcion'));
                $causa = trim($this->input->post('causa'));
                $sql = "update temporal set tempVarchar1 = '{$this->input->post('tipo')}',";
                $sql .= "tempText1 = '$disca',";
                $sql .= "tempText2 = '$causa' where tempId = '{$this->input->post('tempId')}'";
                $this->db->query($sql);
                break;
            case 'D': //actualiza el registro
                $sql = "delete from temporal where tempId = '$tran'";
                $this->db->query($sql);
                break;
            default:
                break;
        }
    }

    public function ayudasTec($opc, $tran = NULL) {
        $tran = (isset($tran)) ? $tran : $this->input->post('transaccion');
        switch ($opc) {
            case 'C':
                $obs = trim($this->input->post('observacion'));
                $sql = "insert into temporal(tempVarchar1,tempVarchar2,tempText1,transaccion,tempInd) values(";
                $sql .= "'{$this->input->post('clases')}','{$this->input->post('ayuda')}','$obs','{$this->input->post('transaccion')}','ayudasTecnicas')";
                $this->db->query($sql);
                break;
            case 'E':
                $sql = "select tempVarchar1,tempVarchar2,tempVarchar3,tempVarchar4,tempText1,transaccion,tempId from temporal where tempId = '$tran'";
                $query = $this->db->query($sql);
                $obj = $query->result();
                echo json_encode($obj[0]);
                break;
            case 'A': //actualiza el registro
                $obs = trim($this->input->post('observacion'));
                $sql = "update temporal set tempVarchar1 = '{$this->input->post('clases')}',";
                $sql .= "tempVarchar2 = '{$this->input->post('ayuda')}',";
                $sql .= "tempText1 = '$obs' where tempId = '{$this->input->post('tempId')}'";
                $this->db->query($sql);
                break;
            case 'D': //actualiza el registro
                $sql = "delete from temporal where tempId = '$tran'";
                $this->db->query($sql);
                break;
            default:
                break;
        }
    }

    public function adminSalud($opc, $tran = NULL) {
        $tran = (isset($tran)) ? $tran : $this->input->post('transaccion');
        switch ($opc) {
            case 'C':
                $cont = trim($this->input->post('contrato'));
                $sql = "insert into temporal(tempVarchar1,tempInt1,tempVarchar2,tempVarchar3,tempDate1,tempDate2,transaccion,tempInd,tempInt2,tempVarchar4,tempVarchar5) values(";
                $sql .= "'{$this->input->post('tipo')}','{$this->input->post('adminis')}','$cont','{$this->input->post('actual')}','{$this->input->post('fecIni')}','{$this->input->post('fecFin')}','{$this->input->post('transaccion')}','administraPersonas','{$this->input->post('tiempoPension')}','{$this->input->post('regimen')}','{$this->input->post('pension')}')";
                $this->db->query($sql);
                break;
            case 'E':
                $sql = "select tempVarchar1,tempVarchar2,tempVarchar3,tempInt1,tempDate1,tempDate2,transaccion,tempId,tempInt2,tempVarchar4,tempVarchar5 from temporal where tempId = '$tran'";
                $query = $this->db->query($sql);
                $obj = $query->result();
                echo json_encode($obj[0]);
                break;
            case 'A': //actualiza el registro
                $cont = trim($this->input->post('contrato'));
                $sql = "update temporal set tempVarchar1 = '{$this->input->post('tipo')}',";
                $sql .= "tempInt1 = '{$this->input->post('adminis')}',";
                $sql .= "tempVarchar2 = '$cont',";
                $sql .= "tempVarchar3 = '{$this->input->post('actual')}',";
                $sql .= "tempDate1 = '{$this->input->post('fecIni')}',";
                $sql .= "tempInt2 = '{$this->input->post('tiempoPension')}',";
                $sql .= "tempVarchar4 = '{$this->input->post('regimen')}',";
                $sql .= "tempVarchar5 = '{$this->input->post('pension')}',";
                $sql .= "tempDate2 = '{$this->input->post('fecFin')}' where tempId = '{$this->input->post('tempId')}'";
                $this->db->query($sql);
                break;
            case 'D': //actualiza el registro
                $sql = "delete from temporal where tempId = '$tran'";
                $this->db->query($sql);
                break;
            default:
                break;
        }
    }

    public function countTemporal($opc, $transaccion) {
        $sql = "select count(transaccion) as total from temporal where tempInd = '$opc' and transaccion = '$transaccion'";
        $query = $this->db->query($sql);
        $obj = $query->result();
        return $obj[0]->total;
    }

    public function cambiaContra() {
        $sql = "select * from usuarios where usuarioId = '{$this->session->userdata('usuarioId')}' and usuarioClave = md5('{$this->input->post("actual")}')";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $sql = "update usuarios set usuarioClave = md5('{$this->input->post("nueva2")}') where usuarioId = '{$this->session->userdata('usuarioId')}'";
            $query = $this->db->query($sql);
            return "1";
        }
        else
            return "0";
    }

    public function guardaAdmin() {
        $this->db->trans_start(); //inicia la transaccion
        $sql = "update administrador set ";
        $sql .= "administradorNombres = '{$this->input->post("nombre")}',";
        $sql .= "administradorApellidos = '{$this->input->post("apellidos")}' where administradorId = '{$this->session->userdata("tablaUsuarioId")}'";
        $this->db->query($sql);
        $sql = "update usuarios set ";
        $sql .= "usuario = '{$this->input->post("usuario")}' where usuarioId = '{$this->session->userdata("usuarioId")}'";
        $this->db->query($sql);
        $this->db->trans_complete();
        return;
    }

    public function getServInclu() {
        /* Funcion para obtener todos los servicios de inclusion que se encuentran activos en el sistema */
        $sql = "select * from serviciosinclusion where `servInclusionEstado` = 'A' order by `servInclusionNombre`";
        return $this->db->query($sql);
    }

//fin public function getServInclu(){
//fin public function getServInclu(){

    public function getServIncluInstitucion($institucionId) {
        /* Funcion para obtener todos los servicios de inclusion tiene la institucion */
        $sql = "select * from instituciones_por_serviciosinclusion where `institucionId` = '$institucionId'";
        return $this->db->query($sql);
    }

//fin public function getServIncluInstitucion(){
    public function creaPruebaSolicitud($institucion, $empresaId) {
        /* Registra en la bd que la empresa quizo contactar con la institucion para un servicio */
        $sql = "insert into registroSolicitud(institucionId,empresaId,registroFechaCreo) values('$institucion','$empresaId',now())";
        $this->db->query($sql);
        return;
    }

//fin public function creaPruebaSolicitud($institucion,$empresaId){
}

?>
